Splitting Data Using Text to Columns in Excel

Course: Advanced Excel

Topic: Splitting Data Using Text to Columns

Course Link: Advanced Excel Course – JustAcademy

Demo Link: Register for a Course Demo

Website: JustAcademy Official Website

1. Introduction to Text to Columns in Excel

Text to Columns is an Excel feature that separates text stored in a single column into multiple columns. It is especially useful when imported or manually entered data contains several pieces of information in one cell, such as a full name, address, product code, date, or transaction reference.

For example, a cell containing Amit Shah can be split into two separate columns: First Name and Last Name. Similarly, a cell containing Product101,Electronics,2500 can be divided into Product ID, Category, and Price by using the comma as a delimiter.

This feature is commonly used in data cleaning, report preparation, database exports, customer list management, sales analysis, and business reporting.

2. Learning Objectives

• Understand the purpose and applications of Text to Columns.
• Differentiate between delimited and fixed-width data.
• Split data using commas, spaces, tabs, semicolons, and custom delimiters.
• Separate names, addresses, codes, and imported text into different columns.
• Configure destination cells and column data formats.
• Prevent data loss when splitting records.
• Troubleshoot common splitting problems.
• Understand when to use Text to Columns, formulas, Flash Fill, or Power Query.

3. Why Is Splitting Data Important?

Data often arrives in a format that is convenient for storage or export but unsuitable for analysis. Excel works more effectively when individual pieces of information are stored in separate fields.

Separating these values makes sorting, filtering, validation, lookup formulas, PivotTables, and reporting easier. However, the delimiter and structure must be understood before splitting because incorrect settings can separate data in unintended ways.

4. Understanding the Text to Columns Feature

The Text to Columns command is available in the Data tab of Excel. It provides a wizard that guides you through selecting a splitting method, choosing separators or column positions, setting output formats, and selecting a destination.

Two Main Splitting Methods

5. Method 1: Splitting Data Using Delimited Text

The Delimited option is appropriate when values are separated by a consistent character, such as a comma, tab, semicolon, space, or another symbol.

Step-by-Step Procedure

1. Open the Excel workbook containing the data.
2. Select the cells in the column that you want to split.
3. Go to the Data tab.
4. Click Text to Columns in the Data Tools group.
5. In Step 1 of the wizard, choose Delimited.
6. Click Next.
7. Select the delimiter used in your data, such as Comma, Tab, Semicolon, or Space.
8. If required, select Other and enter a custom separator.
9. Review the Data Preview to confirm that the values are split correctly.
10. Click Next to configure the output format if necessary.
11. Select the destination cell if you do not want to overwrite the original column.
12. Click Finish to complete the operation.

Example: Splitting Full Names

Suppose column A contains full names separated by spaces.

To perform this split, select the names, choose Data > Text to Columns, select Delimited, and check Space. Review the preview before clicking Finish.

Important: Splitting by spaces may produce more than two output columns when a person has a middle name or a multi-word surname. For example, "Priya Rani Sharma" may be split into three columns. Review the structure of the names before applying this method to an entire dataset.

6. Method 2: Splitting Data Using a Comma Delimiter

A comma is commonly used in CSV files and exported business data. If multiple values appear in a cell and are separated by commas, Text to Columns can divide them into individual fields.

Example: Product Information

Suppose cells in column A contain the following records:

101,Electronics,2500
102,Furniture,4500
103,Stationery,500

After selecting Comma as the delimiter, the result will look like this:

Procedure

1. Select the source column.
2. Open Data > Text to Columns.
3. Choose Delimited.
4. Select Comma and clear any other delimiter options that are not required.
5. Review the preview for the correct number of columns.
6. Choose an output destination and finish the wizard.

This method is helpful for product lists, customer exports, sales records, and structured text copied from other applications.

7. Method 3: Splitting Data Using a Space Delimiter

The Space delimiter separates text wherever a space character occurs. It is useful for splitting simple names, city-state combinations, and other text fields that follow a predictable structure.

Example

Procedure

1. Select the cells that contain the text.
2. Open the Text to Columns wizard.
3. Select Delimited and click Next.
4. Choose Space as the delimiter.
5. Check the Data Preview to confirm the number of resulting columns.
6. Choose a destination and click Finish.

Limitation: A space delimiter does not understand the meaning of words. A city or address containing multiple words may be divided into several columns. If the data does not follow a consistent pattern, consider Flash Fill, formulas, or Power Query instead.

8. Method 4: Splitting Data Using a Custom Delimiter

A custom delimiter is a character or symbol that is not included among the standard delimiter options. Examples include a hyphen, slash, pipe symbol, colon, or underscore.

Example: Employee IDs

Suppose employee IDs follow this format:

EMP-2026-015
EMP-2026-016
EMP-2026-017

To split the IDs into three parts:

1. Select the employee ID column.
2. Open Data > Text to Columns.
3. Choose Delimited.
4. Select Other and enter a hyphen (-).
5. Review the preview to confirm that each ID is divided into three fields.
6. Choose a safe destination and click Finish.

The output will be:

When an identifier contains leading zeros, such as 015, choose Text as the column data format if you need to preserve those zeros. If Excel interprets the value as a number, the leading zero may disappear.

9. Method 5: Splitting Fixed-Width Data

The Fixed Width option divides data at selected character positions rather than at a separator. It is useful when imported records follow a consistent character layout.

Example

Consider the following records:

ABC20260015
XYZ20260016
PQR20260017

Assume the first three characters represent a department code, the next four represent a year, and the final three represent a sequence number.

Steps to Split Fixed-Width Data

1. Select the source column.
2. Go to Data > Text to Columns.
3. Choose Fixed Width and click Next.
4. In the Data Preview, click to insert break lines at the required character positions.
5. Drag existing break lines to adjust them, or double-click a break line to remove it.
6. Click Next and configure the output data formats.
7. Select a destination and click Finish.

Important: Fixed-width splitting works best when all records use the same character positions. If some records have different lengths or inconsistent layouts, the output may be incorrect.

10. Configuring Column Data Formats

The Text to Columns wizard lets you specify how each output column should be interpreted. Correct formatting is important because Excel may otherwise convert identifiers, dates, and other values automatically.

Example: Preserving Product Codes

Suppose a product code is stored as 000456. If Excel interprets it as a number, the leading zeros may be removed. Selecting Text for the relevant output column preserves the identifier exactly as required.

Example: Splitting Dates

Imported dates can be interpreted differently depending on regional settings. If a field contains date values, use the Date option and select the appropriate order, such as DMY, MDY, or YMD. Always check the preview and verify the resulting dates after the operation.

11. Choosing the Output Destination

The Destination field specifies where Excel should place the split results. Choosing the correct destination helps prevent accidental data loss.

• Same location: Excel places the output starting at the selected source location. This may overwrite adjacent data.
• Different location: Enter a destination such as D2 to preserve the original source column.
• Separate worksheet: For a safer workflow, copy the source data to another worksheet before splitting it.

If the split produces multiple columns, ensure that the destination area has enough empty columns. Excel may overwrite existing values in those cells without giving you the result you intended.

12. Practical Example: Splitting Customer Contact Data

Suppose a customer list contains full names and contact information in a combined text column.

Using Comma as the delimiter separates the records into three fields. The result can then be organized into dedicated columns.

Once the data is separated, you can sort customers by city, filter email addresses, perform lookups, and create PivotTables. If full names themselves need to be separated further, perform an additional split or use a suitable formula.

13. Text to Columns vs. Other Excel Methods

14. Using the TEXTSPLIT Function as an Alternative

In supported versions of Microsoft 365 and newer Excel releases, TEXTSPLIT can separate text using a delimiter directly in a formula. This is useful when you want a dynamic result instead of using the Text to Columns wizard.

Syntax

=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty],[match_mode],[pad_with])

Example 1: Split Text Using a Comma

If A2 contains Amit,Shah,Mumbai, enter:

=TEXTSPLIT(A2,",")

The formula returns the separated values into adjacent columns, provided the spill area is clear.

Example 2: Split an Employee Code Using a Hyphen

=TEXTSPLIT(A2,"-")

If A2 contains EMP-2026-015, the formula returns EMP, 2026, and 015 as separate text values.

Example 3: Ignore Empty Values Between Delimiters

If A2 contains Amit,,Mumbai, use:

=TEXTSPLIT(A2,",",,TRUE)

This instructs Excel to ignore empty values between consecutive delimiters. Use this option only when empty fields should not be retained in the output.

15. Common Problems and Troubleshooting

16. Best Practices for Using Text to Columns

• Always keep a backup of important source data.
• Inspect sample records before splitting an entire column.
• Choose the delimiter that reflects the actual structure of the data.
• Use the Data Preview to check the output before finishing.
• Set identifiers containing leading zeros to Text.
• Check date formats carefully to avoid incorrect date interpretation.
• Choose a destination with sufficient empty columns.
• Verify the number of output columns and the accuracy of the resulting records.
• For repeated monthly or weekly imports, consider Power Query to make the process reusable.
• For dynamic formula-based extraction, use TEXTSPLIT when available and appropriate.

17. Practical Exercises

Exercise 1: Split Full Names

Enter the following names into column A: Amit Shah, Neha Patel, Rahul Mehta, and Priya Joshi. Use Text to Columns with Space as the delimiter.

Expected outcome: The first and last names appear in separate columns for these two-part names.

Exercise 2: Split Product Records

Create a list containing product ID, category, and price separated by commas. Use the Comma delimiter to split the records into three columns.

Expected outcome: Each product field is stored separately.

Exercise 3: Split Employee Codes

Enter EMP-2026-015, EMP-2026-016, and EMP-2026-017. Use the hyphen as a custom delimiter.

Expected outcome: Prefix, year, and sequence number appear in separate columns.

Exercise 4: Split Fixed-Width Records

Create codes with a three-character department code, four-character year, and three-character sequence number. Use Fixed Width and insert breaks at the correct positions.

Expected outcome: Each code is divided into the intended fields.

Exercise 5: Compare Text to Columns and TEXTSPLIT

Split a list of comma-separated names using Text to Columns. Repeat the exercise with the TEXTSPLIT formula in a supported Excel version.

Expected outcome: Understand the difference between a one-time transformation and a dynamic formula result.

18. Interview Questions and Answers

Q1. What is Text to Columns in Excel?

Answer: Text to Columns is a built-in Excel feature that divides text from one column into multiple columns using delimiters or fixed character positions.

Q2. What are the two main options in Text to Columns?

Answer: Delimited and Fixed Width. Delimited uses separators such as commas or spaces, while Fixed Width uses specified character positions.

Q3. What is a delimiter?

Answer: A delimiter is a character that separates values, such as a comma, tab, semicolon, space, or hyphen.

Q4. Can Text to Columns split employee codes?

Answer: Yes. It can split codes using a custom delimiter or fixed character positions, depending on the code structure.

Q5. How can you prevent leading zeros from disappearing?

Answer: Set the relevant output column to Text in the Text to Columns wizard before finishing the operation.

Q6. Does Text to Columns update automatically when new records are added?

Answer: No. Text to Columns is generally a one-time transformation. Use formulas such as TEXTSPLIT or a Power Query workflow when repeatable or dynamic results are required.

Q7. What is the difference between Text to Columns and TEXTSPLIT?

Answer: Text to Columns transforms selected worksheet data through a wizard. TEXTSPLIT uses a formula to return a dynamic array of separated values in supported Excel versions.

Q8. What happens when a space delimiter is applied to a multi-word address?

Answer: Every space may create a split point, which can divide the address into several columns. The output should be reviewed carefully before completing the operation.

Q9. Why is the Destination field important?

Answer: It controls where the split values are placed. Choosing an empty destination helps protect the original data and prevents accidental overwriting.

Q10. When should Power Query be used instead?

Answer: Power Query is useful when data must be imported and split repeatedly, especially for recurring reports and larger datasets that require a repeatable cleaning process.

19. Quick Revision Checklist

• Text to Columns splits one column into multiple columns.
• Delimited splitting uses separators such as commas, tabs, spaces, and custom symbols.
• Fixed Width splits text at specified character positions.
• Data Preview helps verify the split before finishing.
• The destination should have enough empty cells to receive the output.
• Text formatting preserves identifiers with leading zeros.
• Date formatting should match the source date order.
• TEXTSPLIT provides a formula-based alternative in supported versions.
• Power Query is suitable for repeatable data-cleaning workflows.
• Always validate the output and preserve a backup of important data.

20. Conclusion

Splitting data using Text to Columns is an important Advanced Excel skill that helps transform combined text into structured, analysis-ready information. By using delimiters, fixed-width positions, correct output formats, and suitable destinations, you can efficiently organize names, product codes, customer records, and imported datasets.

For one-time data cleanup, Text to Columns provides a quick and accessible solution. For dynamic splitting, TEXTSPLIT may be more suitable, while Power Query offers a repeatable approach for recurring data imports. Choosing the right method and checking the results carefully helps maintain accuracy and prevent data loss.

Learn More with JustAcademy

Explore the following resources to strengthen your Excel and data management skills:

• JustAcademy Official Website – Explore available learning opportunities.
• Advanced Excel Course – Learn more about advanced spreadsheet skills, data cleaning, and reporting.
• Register for a Course Demo – Explore the available training options.